package simorg.domain.persistence;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import simorg.domain.Compound;
import simorg.util.DatabaseConnection;

public class CompoundTable {

	
	public static void bootstrap(boolean drop) throws Exception {
		DatabaseConnection dc = new DatabaseConnection();
		try {
			Statement stmt = null;
		    String query = null;
		    if ( drop ) {
		    	query = "DROP TABLE IF EXISTS COMPOUND";
		    	stmt = dc.getConnection().createStatement();
			    stmt.execute(query);
			}
		    query = "CREATE TABLE IF NOT EXISTS COMPOUND" +
		    	" (ID INT PRIMARY KEY, LABEL VARCHAR(50), CLASS VARCHAR(50), BOILING_POINT DECIMAL, MELTING_POINT DECIMAL)";
		    stmt = dc.getConnection().createStatement();
		    stmt.execute(query);
		   
		   /*  Only for testing - compounds are now loaded via an excel sheet.
		    * 
		    LinkedList<Compound> retval = new LinkedList<Compound>();
			retval.add(new Compound("water"));
			retval.add(new Compound("oil"));
			retval.add(new Compound("ethenol"));
			retval.add(new Compound("nitrogen"));
			retval.add(new Compound("helium"));
			retval.add(new Compound("hydrogen"));
			retval.add(new Compound("carbon"));
			retval.add(new Compound("plutonium"));
			
			for( Compound c : retval ) {
				save(dc, c);
			}
		    
			System.out.println("Bootstrapped Compounds");
		    Collection<Compound> compounds = findAll(dc);
		    for ( Compound cmp : compounds ) {
		    	System.out.println("ID:  " + cmp.getId() + " - " + cmp.getLabel());
		    }*/
		}
		finally {
			dc.getConnection().close();
		}
	}
	
	public static void save(DatabaseConnection dc, Compound compound) {
		try {
			String query = null;
			if ( findById(dc, compound.getId()) == null ) {
				query = "INSERT INTO COMPOUND (ID, LABEL, CLASS, BOILING_POINT, MELTING_POINT) " +
								"VALUES ( ?, ?, ?, ?, ?)";
				PreparedStatement insert = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(compound, insert);
				insert.executeUpdate();
				ResultSet rs = insert.getGeneratedKeys();
				while (rs.next()){
					compound.setId(rs.getInt(1));
				}
			}
			else {
				throw new RuntimeException("Compounds are read-only");
				// update existing course
				/*query = "UPDATE COMPOUND SET ID = ?, LABEL=?, CLASS=?, BOILING_POINT=?, MELTING_POINT=? " +
						"WHERE ID=?";
				PreparedStatement update = dc.getConnection().prepareStatement(query);
				fillPreparedStatement(compound, update);
				update.setInt(6, compound.getId());
				update.executeUpdate();*/
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	
	public static Collection<Compound> findAll(DatabaseConnection db){
		QueryConstraints constraints = new QueryConstraints();
		return find(db, constraints);
	}
	
	public static void fillFromResultSet(Compound c,ResultSet rs) throws Exception {
		c.setId(rs.getInt("ID"));
		c.setLabel(rs.getString("LABEL"));
		c.setClassName(rs.getString("CLASS"));
		c.setBoilingPoint(rs.getFloat("BOILING_POINT"));
		c.setMeltingPoint(rs.getFloat("MELTING_POINT"));
	}

	public static void fillPreparedStatement(Compound c, PreparedStatement stmt) throws Exception {
		stmt.setInt(1, c.getId());
		stmt.setString(2, c.getLabel());
		stmt.setString(3, c.getClassName());;
		stmt.setFloat(4, c.getBoilingPoint());
		stmt.setFloat(5, c.getMeltingPoint());
	}
	
	public static ArrayList<Compound> find(DatabaseConnection dc, QueryConstraints constraints) {
		ArrayList<Compound> retval = new ArrayList<Compound>();
		try {
			String query = "SELECT * FROM COMPOUND " + constraints.getParameterString();
			PreparedStatement stmt = dc.getConnection().prepareStatement(query);
			constraints.fillPreparedStatement(stmt);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()){
				Compound compound = new Compound();
				fillFromResultSet(compound, rs);
		        retval.add(compound);
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return retval;
	}
	
	public static Compound findById(DatabaseConnection db, int id) {
		QueryConstraints constraints = new QueryConstraints();
		constraints.getConstraints().add(new QueryConstraint("ID", id));
		ArrayList<Compound> result = find(db, constraints);
		return result.size() > 0 ? result.get(0) : null;
	}
	
	
	

	
}
